package dao;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class ArtistDaoImpl implements ArtistDao {
    private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";

    private static final String DATABASE_PROPERTIES_FILE = "jdbc.properties";
    private static final String URL_PROPERTY = "url";
    private static final String USERNAME_PROPERTY = "username";
    private static final String PASSWORD_PROPERTY = "password";

    private static final String SELECT_ARTISTS_STATEMENT = "SELECT * FROM artists ORDER BY artist_name";
    private static final String SELECT_ARTIST_BY_ID_STATEMENT = "SELECT * FROM artists WHERE artist_id = ?";

    private static final String SELECT_ARTIST_BY_ALBUM_ID_STATEMENT =
            "SELECT a.artist_id, a.artist_name, a.artist_year, a.artist_description " +
            "FROM artists AS a JOIN albums as b ON a.artist_id = b.album_artist_id " +
            "WHERE b.album_id = ?;";

    private static final String ARTIST_ID_COLUMN = "artist_id";
    private static final String ARTIST_NAME_COLUMN = "artist_name";
    private static final String ARTIST_YEAR_COLUMN = "artist_year";
    private static final String ARTIST_DESCRIPTION_COLUMN = "artist_description";

    private static final ArtistDao INSTANCE = new ArtistDaoImpl();

    private static String url;
    private static String username;
    private static String password;

    static {
        readDatabaseProperties();
    }

    private ArtistDaoImpl() {

    }

    private static void readDatabaseProperties() {
        InputStream in = null;

        try {
            Properties properties = new Properties();
            in = ArtistDaoImpl.class.getResourceAsStream(DATABASE_PROPERTIES_FILE);

            if (in != null) {
                properties.load(in);
            }

            String url = properties.getProperty(URL_PROPERTY);
            String username = properties.getProperty(USERNAME_PROPERTY);
            String password = properties.getProperty(PASSWORD_PROPERTY);

            ArtistDaoImpl.url = url;
            ArtistDaoImpl.username = username;
            ArtistDaoImpl.password = password;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static ArtistDao getInstance() {
        return INSTANCE;
    }

    @Override
    public List<Artist> getArtists() {
        List<Artist> artists = new ArrayList<Artist>();

        Connection connection = getConnection();
        Statement statement = null;

        try {
            statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(SELECT_ARTISTS_STATEMENT);

            while (resultSet.next()) {
                Artist artist = mapResultSet(resultSet);
                artists.add(artist);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return artists;
    }

    @Override
    public Artist getArtistById(Long artistId) {
        Artist artist = null;

        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = connection.prepareStatement(SELECT_ARTIST_BY_ID_STATEMENT);
            preparedStatement.setLong(1, artistId);
            preparedStatement.setMaxRows(1);

            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                artist = mapResultSet(resultSet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return artist;
    }

    @Override
    public Artist getArtistByAlbumId(Long albumId) {
        Artist artist = null;

        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = connection.prepareStatement(SELECT_ARTIST_BY_ALBUM_ID_STATEMENT);
            preparedStatement.setLong(1, albumId);

            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                artist = mapResultSet(resultSet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return artist;
    }

    private Artist mapResultSet(ResultSet resultSet) {
        Artist artist = new Artist();

        try {
            artist.setId(resultSet.getLong(ARTIST_ID_COLUMN));
            artist.setName(resultSet.getString(ARTIST_NAME_COLUMN));
            artist.setYear(resultSet.getInt(ARTIST_YEAR_COLUMN));
            artist.setDescription(resultSet.getString(ARTIST_DESCRIPTION_COLUMN));
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return artist;
    }

    private Connection getConnection() {
        Connection connection = null;

        try {
            Class.forName(DRIVER_CLASS);
            connection = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return connection;
    }
}


